package ccnt.pvc.scudwaremobile.storage;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import ccnt.pvc.scudwaremobile.storage.ServiceContract.ApkEntry;
import ccnt.pvc.scudwaremobile.storage.ServiceContract.ServiceEntry;

public class ServiceDbHelper extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "service.db";
	private static final String TAG = ServiceDbHelper.class.getName();
	
	private static final String SQL_CREATE_APK_TABLE = 
			"CREATE TABLE " + ApkEntry.TABLE_NAME + " (" +
			ApkEntry.COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
			ApkEntry.COLUMN_APK_NAME + " TEXT, " +
			ApkEntry.COLUMN_PACKAGE_NAME + " TEXT, " +
			ApkEntry.COLUMN_VERSION + " TEXT, " +
			ApkEntry.COLUMN_DEVELOPER + " TEXT, " +
			ApkEntry.COLUMN_IS_INSTALLED + " INTEGER, " +
			ApkEntry.COLUMN_GMTCREATED + " DATETIME, " +
			ApkEntry.COLUMN_GMTMODIFIED + " DATETIME, " +
			ApkEntry.COLUMN_IS_DELETED + " INTEGER " +
			")";
	
	private static final String SQL_CREATE_SERVICE_ALL_TABLE =  
			"CREATE TABLE " + ServiceEntry.TABLE_NAME_ALL + " (" +
			ServiceEntry.COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
			ServiceEntry.COLUMN_CLASS_NAME + " TEXT, " +
			ServiceEntry.COLUMN_PACKAGE_NAME + " TEXT, " +
			ServiceEntry.COLUMN_DEVICE_NAME + " TEXT, " +
			ServiceEntry.COLUMN_CATEGORY + " INTEGER, " +
			ServiceEntry.COLUMN_SENSOR_NAME + " TEXT, " +
			ServiceEntry.COLUMN_DATA_TYPE + " TEXT, " +
			ServiceEntry.COLUMN_MEASURE + " TEXT, " +
			ServiceEntry.COLUMN_LEVEL + " INTEGER, " +
			ServiceEntry.COLUMN_PARAMS + " TEXT, " +
			ServiceEntry.COLUMN_DESCRIPTION + " TEXT, " +
			ServiceEntry.COLUMN_GMTCREATED + " DATETIME, " +
			ServiceEntry.COLUMN_GMTMODIFIED + " DATETIME, " +
			ServiceEntry.COLUMN_IS_DELETED + " INTEGER " +
			")";
	
	private static final String SQL_CREATE_SERVICE_REG_TABLE =  
			"CREATE TABLE " + ServiceEntry.TABLE_NAME_REG + " (" +
			ServiceEntry.COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
			ServiceEntry.COLUMN_CLASS_NAME + " TEXT, " +
			ServiceEntry.COLUMN_PACKAGE_NAME + " TEXT, " +
			ServiceEntry.COLUMN_DEVICE_NAME + " TEXT, " +
			ServiceEntry.COLUMN_CATEGORY + " INTEGER, " +
			ServiceEntry.COLUMN_SENSOR_NAME + " TEXT, " +
			ServiceEntry.COLUMN_DATA_TYPE + " TEXT, " +
			ServiceEntry.COLUMN_MEASURE + " TEXT, " +
			ServiceEntry.COLUMN_LEVEL + " INTEGER, " +
			ServiceEntry.COLUMN_PARAMS + " TEXT, " +
			ServiceEntry.COLUMN_DESCRIPTION + " TEXT, " +
			ServiceEntry.COLUMN_GMTCREATED + " DATETIME, " +
			ServiceEntry.COLUMN_GMTMODIFIED + " DATETIME, " +
			ServiceEntry.COLUMN_IS_DELETED + " INTEGER " +
			")";
			
	public static final String SQL_DROP_APK_TABLE = 
			"DROP TABLE IF EXISTS " + ApkEntry.TABLE_NAME;
	
	public static final String SQL_DROP_SERVICE_ALL_TABLE = 
			"DROP TABLE IF EXISTS " + ServiceEntry.TABLE_NAME_ALL;
	
	public static final String SQL_DROP_SERVICE_REG_TABLE = 
			"DROP TABLE IF EXISTS " + ServiceEntry.TABLE_NAME_REG;
	
	public ServiceDbHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.d(TAG, "Create Database");
		db.execSQL(SQL_CREATE_APK_TABLE);
		db.execSQL(SQL_CREATE_SERVICE_ALL_TABLE);
		db.execSQL(SQL_CREATE_SERVICE_REG_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.d(TAG, "Upgrade Database");
		db.execSQL(SQL_DROP_APK_TABLE);
		db.execSQL(SQL_DROP_SERVICE_ALL_TABLE);
		db.execSQL(SQL_DROP_SERVICE_REG_TABLE);
		onCreate(db);
	}

}
